﻿<%@ WebHandler Language="C#" Class="UnqualifiedHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

//Description:不合格特征、原因处理程序
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:longb
//create Date:2023-11-15

public class UnqualifiedHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "getUnqualifiedFeature": //获取不合格特征数据
                    getUnqualifiedFeature(context);
                    break;
                case "saveUnqualifiedFeature": //保存不合格特征数据
                    saveUnqualifiedFeature(context);
                    break;
                case "deleteUnqualifiedFeature": //删除不合格特征数据
                    deleteUnqualifiedFeature(context);
                    break;
                case "getUnqualifiedReason": //获取不合格原因数据
                    getUnqualifiedReason(context);
                    break;
                case "getAllUnqualifiedReason": //获取所有不合格原因数据
                    getAllUnqualifiedReason(context);
                    break;
                case "saveUnqualifiedReason": //保存不合格原因数据
                    saveUnqualifiedReason(context);
                    break;
                case "deleteUnqualifiedReason": //删除不合格原因数据
                    deleteUnqualifiedReason(context);
                    break;
                case "getSpecUnqualifiedReason"://查询工序不合格数据
                    getSpecUnqualifiedReason(context);
                    break;
                case"saveSpecUnqualifiedReasonCode"://保存工序不合格原因代码
                    saveSpecUnqualifiedReasonCode(context);
                    break;
                case "getOneInspectionPassRate"://一次交检合格率打印
                    getOneInspectionPassRate(context);
                    break;      
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

    private void getUnqualifiedFeature(HttpContext context) 
    {
        string feature = context.Request["feature"].ToString();
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        string selectSQL = @"SELECT U.*,e.FULLNAME CreateNAME,e2.FULLNAME LastChangeNAME,f.FACTORYNAME 
                FROM UnqualifiedFeature U 
                LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =U.CREATEEMPLOYEEID 
                LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =U.LASTCHANGEEMPLOYEEID 
                LEFT JOIN FACTORY f ON f.FACTORYID =U.FACTORYID
                WHERE U.ISuse=0
                ";
        if (!string.IsNullOrWhiteSpace(feature)) {
            selectSQL += " and UnqualifiedFeatureNAME like '%" + feature + "%'";
        }
        selectSQL += "order by to_number(U.UnqualifiedFeatureNO)";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = selectSQL;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void saveUnqualifiedFeature(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var insertSQL = @"insert into UnqualifiedFeature (UnqualifiedFeatureID,UnqualifiedFeatureNO,UnqualifiedFeatureNAME,FACTORYID,CreateEmployeeID,CreateDAte)values('{0}','{1}','{2}','{3}','{4}',sysdate)";
        var arrList = new ArrayList();
        foreach(DataRow row in data.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["UnqualifiedFeatureID"].ToString()))
            {
                arrList.Add(string.Format(insertSQL, Guid.NewGuid(), row["UnqualifiedFeatureNO"], row["UnqualifiedFeatureNAME"],m_UserInfo.FactoryID,m_UserInfo.EmployeeID));
            }else{
                arrList.Add(string.Format("update UnqualifiedFeature set UnqualifiedFeatureNAME='{0}',LastChangeEmployeeId='{1}',LastChangeDate=sysdate where UnqualifiedFeatureID='{2}'", row["UnqualifiedFeatureNAME"], m_UserInfo.FactoryID, row["UnqualifiedFeatureID"]));
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count>0) {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
       result.Message= result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    private void deleteUnqualifiedFeature(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            arrList.Add(string.Format("update UnqualifiedFeature set isuse=1 where UnqualifiedFeatureID='{0}'", row["UnqualifiedFeatureID"]));
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    
    private void getUnqualifiedReason(HttpContext context)
    {
        var para = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        string selectSQL = @"SELECT U.*,e.FULLNAME CreateNAME,e2.FULLNAME LastChangeNAME,f.FACTORYNAME 
                FROM UnqualifiedReason U 
                LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =U.CREATEEMPLOYEEID 
                LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =U.LASTCHANGEEMPLOYEEID 
                LEFT JOIN FACTORY f ON f.FACTORYID =U.FACTORYID
                WHERE U.ISuse=0
                ";
        if (para.ContainsKey("REASONCODE"))
        {
            if(!string.IsNullOrWhiteSpace(para["REASONCODE"])){
                selectSQL += " and REASONCODE like '%" + para["REASONCODE"] + "%'";
            }
        }
        if (para.ContainsKey("REASONSORT"))
        {
            if (!string.IsNullOrWhiteSpace(para["REASONSORT"]))
            {
                selectSQL += " and REASONSORT like '%" + para["REASONSORT"] + "%'";
            }
        }
        if (para.ContainsKey("REASONSUBCLASS"))
        {
            if (!string.IsNullOrWhiteSpace(para["REASONSUBCLASS"]))
            {
                selectSQL += " and REASONSUBCLASS like '%" + para["REASONSUBCLASS"] + "%'";
            }
        }

        if (para.ContainsKey("UNIQUECODE"))
        {
            if (!string.IsNullOrWhiteSpace(para["UNIQUECODE"]))
            {
                selectSQL += " and UNIQUECODE like '%" + para["UNIQUECODE"] + "%'";
            }
        }
        if (para.ContainsKey("REASONCODENAME"))
        {
            if (!string.IsNullOrWhiteSpace(para["REASONCODENAME"]))
            {
                selectSQL += " and REASONCODENAME like '%" + para["REASONCODENAME"] + "%'";
            }
        }
        if (para.ContainsKey("REASONSORTDES"))
        {
            if (!string.IsNullOrWhiteSpace(para["REASONSORTDES"]))
            {
                selectSQL += " and REASONSORTDES like '%" + para["REASONSORTDES"] + "%'";
            }
        }
        if (para.ContainsKey("REASONSUBCLASSDES"))
        {
            if (!string.IsNullOrWhiteSpace(para["REASONSUBCLASSDES"]))
            {
                selectSQL += " and REASONSUBCLASSDES like '%" + para["REASONSUBCLASSDES"] + "%'";
            }
        }
        
        selectSQL += "order by U.UNIQUECODE";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = selectSQL;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void getAllUnqualifiedReason(HttpContext context) 
    {
        string selectSQL = @"SELECT U.UNQUALIFIEDFEATUREID,U.UNQUALIFIEDFEATURENAME,f.FACTORYNAME 
                FROM UnqualifiedFeature U 
                LEFT JOIN FACTORY f ON f.FACTORYID =U.FACTORYID
                WHERE U.ISuse=0
                ";
        var dt = DatabaseHelper.Query_ReturnDatTable(selectSQL);
        //var newRow = dt.NewRow();
        //newRow["UNQUALIFIEDREASONID"] = "";
        //newRow["REASONCODENAME"] = "\u3000";
        //dt.Rows.InsertAt(newRow,0);
        context.Response.Write(JsonConvert.SerializeObject(dt));
    }
    private void saveUnqualifiedReason(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        var para = JsonConvert.DeserializeObject<Dictionary<string,string>>(context.Request["data"].ToString());
        DataTable dt = DatabaseHelper.Query_ReturnDatTable("select * from UnqualifiedReason where ISuse=0 and UNIQUECODE='" + para["UNIQUECODE"] + "'");
        if (dt.Rows.Count > 0 && string.IsNullOrWhiteSpace(para["UNQUALIFIEDREASONID"])) { result.Message = "唯一代码已存在，请先调整"; }
        else
        {
            var excuteSQL = "";
            if (string.IsNullOrWhiteSpace(para["UNQUALIFIEDREASONID"]))
            {
                var insert = @"insert into UnqualifiedReason(UnqualifiedReasonID,UNIQUECODE,REASONCODE,REASONCODENAME,REASONSORT,REASONSORTDES,REASONSUBCLASS,REASONSUBCLASSDES,CreateDAte,CreateEmployeeID,FACTORYID)
                                                        values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}','{10}')";
                excuteSQL = string.Format(insert, Guid.NewGuid(), para["UNIQUECODE"].ToString(), para["REASONCODE"].ToString(), para["REASONCODENAME"].ToString(), para["REASONSORT"].ToString(), para["REASONSORTDES"].ToString(), para["REASONSUBCLASS"].ToString(), para["REASONSUBCLASSDES"].ToString(), "sysdate", m_UserInfo.EmployeeID, m_UserInfo.FactoryID);
            }
            else
            {
                var update = @"update UnqualifiedReason set UNIQUECODE='{0}',REASONCODE='{1}',REASONCODENAME='{2}',REASONSORT='{3}',REASONSORTDES='{4}',REASONSUBCLASS='{5}',REASONSUBCLASSDES='{6}',LastChangeDate={7},LastChangeEmployeeId='{8}' where UnqualifiedReasonID='{9}'";
                excuteSQL = string.Format(update, para["UNIQUECODE"].ToString(), para["REASONCODE"].ToString(), para["REASONCODENAME"].ToString(), para["REASONSORT"].ToString(), para["REASONSORTDES"].ToString(), para["REASONSUBCLASS"].ToString(), para["REASONSUBCLASSDES"].ToString(), "sysdate", m_UserInfo.EmployeeID, para["UNQUALIFIEDREASONID"]);
            }
            result.Result = DatabaseHelper.ExecuteSql(excuteSQL) > 0;
            result.Message = result.Result ? "保存成功" : "保存失败";
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    private void deleteUnqualifiedReason(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            arrList.Add(string.Format("update UnqualifiedReason set isuse=1 where UnqualifiedReasonID='{0}'", row["UnqualifiedReasonID"]));
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void getSpecUnqualifiedReason(HttpContext context)
    {
        var para = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        string selectSQL = @"select c2.mfgordername,c2.containername,c2.productname,c2.productrevision,c2.productdescription,c2.workflowname ||':'||c2.workflowrevision workflowinfo
,w.workflowstepname,nvl(s.specno,s3.specno) specno,e2.fullname unqualifiedreasoncodeempname,e.fullname checkername,c.reworkqty,c.unqualifiedfeatureids,null unqualifiedfeature,c.containercheckinfoid,c.unqualifiedreasoncode 
from containercheckinfo c
left join containercurrentinfo c2 on c2.containerid =c.containerid 
left join workflowstep w on w.workflowstepid =c.workflowstepid
left join spec s on s.specid =w.specid
left join specbase s2 on s2.specbaseid =w.specbaseid 
left join spec s3 on s3.specbaseid =s2.specbaseid and s3.specid =s2.revofrcdid 
left join employee e on e.employeeid =c.checkerid
left join employee e2 on e2.employeeid =c.unqualifiedreasoncodeempid 
where c.unqualifiedfeatureids is not null   ";
        if (!string.IsNullOrWhiteSpace(para["CONTAINERNO"]))
        {
            selectSQL += " and c2.CONTAINERNAME like '%" + para["CONTAINERNO"] + "%'";
        }
        if (!string.IsNullOrWhiteSpace(para["SPENO"]))
        {
            selectSQL += " and nvl(s.SPECNO,s3.SPECNO) like '%" + para["SPENO"] + "%'";
        }
        
        if (!string.IsNullOrWhiteSpace(para["UNIQUECODE"]))
        {
            selectSQL += " and unqualifiedreasoncode like '%" + para["UNIQUECODE"] + "%'";
        }
        if (!string.IsNullOrWhiteSpace(para["UNQUALIFIEDFEATURE"]))
        {
            DataTable udt = DatabaseHelper.Query_ReturnDatTable(string.Format("SELECT u.UNQUALIFIEDFEATUREID FROM UNQUALIFIEDFEATURE u WHERE u.UNQUALIFIEDFEATURENAME LIKE '%{0}%'", para["UNQUALIFIEDFEATURE"]));
            var unqualifiedfeatureids = string.Empty;
            if (udt.Rows.Count>0) {
                foreach(DataRow row in udt.Rows)
                {
                    unqualifiedfeatureids += " c.unqualifiedfeatureids like '%" + row["UNQUALIFIEDFEATUREID"] + "%' or";
                }
            }
            if (!string.IsNullOrWhiteSpace(unqualifiedfeatureids))
            {
                selectSQL += "and (" + unqualifiedfeatureids.TrimEnd('r').TrimEnd('o') + ")";
            }
            else {
                selectSQL += "and c.unqualifiedfeatureids like '%" + para["UNQUALIFIEDFEATURE"] + "%'";
            }
        }
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = selectSQL;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        foreach(DataRow row in result.DBTable.Rows)
        {
            DataTable dt = DatabaseHelper.Query_ReturnDatTable(string.Format("SELECT to_char(wm_concat(UNQUALIFIEDFEATURENAME)) UNQUALIFIEDFEATURE FROM UNQUALIFIEDFEATURE WHERE UNQUALIFIEDFEATUREID IN('{0}')", row["unqualifiedfeatureids"].ToString().Replace(",", "','")));
            row["UNQUALIFIEDFEATURE"] = dt.Rows[0]["UNQUALIFIEDFEATURE"];
        }
        
        
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    private void saveSpecUnqualifiedReasonCode(HttpContext context) 
    {
        var codes=context.Request["codes"].ToString();
        var checkinfoid = context.Request["checkinfoid"].ToString();
        var updateSQL = @"update containercheckinfo set unqualifiedreasoncode='" + codes + "',unqualifiedreasoncodeempid='"+m_UserInfo.EmployeeID+"' where containercheckinfoid='" + checkinfoid + "'";
        uMESProcessResult result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSql(updateSQL) > 0;
        result.Message = result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void getOneInspectionPassRate(HttpContext context) 
    {
        var cid = context.Request["cid"].ToString();
        string selectSQL = @"SELECT U.*
                FROM UnqualifiedFeature U 
                WHERE U.ISuse=0 order by to_number(U.UnqualifiedFeatureNO)
                ";

        string selectSQL2 = @"select c2.productname,c2.productdescription,c2.WORKCENTERNAME,c2.CONTAINERNO
,nvl(s.specno,s3.specno) specno,e.fullname checkername,to_char(c.CREATEDATE,'yyyy-MM-dd') CREATEDATE
,c.CHECKQTY ,c.CHECKQTY -c.ACCEPTANCEQTY NOqty
from containercheckinfo c
left join containercurrentinfo c2 on c2.containerid =c.containerid 
left join workflowstep w on w.workflowstepid =c.workflowstepid
left join spec s on s.specid =w.specid
left join specbase s2 on s2.specbaseid =w.specbaseid 
left join spec s3 on s3.specbaseid =s2.specbaseid and s3.specid =s2.revofrcdid 
left join employee e on e.employeeid =c.checkerid
where c.unqualifiedfeatureids is not NULL AND c.CONTAINERID IN ('{0}')";
        uMESProcessResult result = new uMESProcessResult();
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(selectSQL);
        result.ReturnData = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL2,cid.Replace(",","','")));
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

}